package main.java.com.action;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import main.java.com.domain.User;
import main.java.com.util.ConnDb;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * !!! Anti-fake label !!!
 * ClassName: StudentInfoActioon
 * Package: main.java.com.action
 * Description:
 *
 * @Author 22软件6班 陈彬
 * @Create 2023/12/13 8:15
 * @Version 1.0
 **/

@WebServlet("/StudentInfoServlet")
public class StudentInfoAction extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //获取菜单路径
        String menuUrl = request.getParameter("menuUrl");
        //将学生信息管理页面（子页面）写入到Session
        request.getSession().setAttribute("menuUrl", menuUrl + ".jsp");
        if (menuUrl != null && menuUrl.equals("studentInfo-list")) {
            //获取接口拼接的参数：专业和班级
            String professionId=request.getParameter("professionId");
            String classId=request.getParameter("classId");
            List<HashMap> list = new ArrayList<>();
            //调用查询学生信息方法
            list=this.queryStudentInfo(professionId,classId);
            request.getSession().setAttribute("studentList", list);
            //把查询参数放入session
            request.getSession().setAttribute("professionId",professionId);
            request.getSession().setAttribute("classId",classId);
        }
        //getRequestDispatchar获取转发对象，跳转框架页面（主页面）
        request.getRequestDispatcher("/WEB-INF/jsp/shellPage.jsp").forward(request, response);
    }
    //查询学生信息方法
    public List<HashMap>
    queryStudentInfo(String professionId,String classId) {
        List<HashMap> list=new ArrayList<>();
        try{
            //调用连接数据库方法
            ConnDb db = new ConnDb();
            Connection conn = db.dataBaseConnectionTest();
            //构建sql
            String sql = "SELECT\n" +
                    "a.stuNo,\n" +
                    "a.name,\n" +
                    "a.sex,\n" +
                    "a.nativePlace,\n" +
                    "a.phone,\n" +
                    "a.professionId,\n" +
                    "b.name AS professionName,\n" +
                    "a.classId,\n" +
                    "c.name AS className,\n" +
                    "DATE_FORMAT(a.updateTime,'%Y-%m-%d %H:%i:%s')AS updateTime\n" +
                    "FROM tb_student_info a\n" +
                    "LEFT JOIN tb_dic_profession b ON a.professionId=b.id\n" +
                    "LEFT JOIN tb_dic_class c ON a.classId=c.id WHERE 1=1";
            //拼接专业id的参数
            if (!"".equals(professionId) && professionId!=null){
                sql=sql+" and a.professionId="+professionId;
            }
            //拼接班级id的参数
            if (!"".equals(classId) && classId!=null){
                sql=sql+" and a.classId="+classId;
            }
            //利用PreparedStatement预处理接口创建对象pstm
            PreparedStatement pstm = conn.prepareStatement(sql);
            //执行方法
            ResultSet rs = pstm.executeQuery();
            while (rs.next()){//如果查询到数据，则进入while循环
                HashMap map=new HashMap(16);
                map.put("stuNo",rs.getString("stuNo"));//取出学号放入map
                map.put("name",rs.getString("name"));
                map.put("sex",rs.getString("sex"));
                map.put("nativePlace",rs.getString("nativePlace"));
                map.put("phone",rs.getString("phone"));
                map.put("professionId",rs.getInt("professionId"));
                map.put("professionName",rs.getString("professionName"));
                map.put("classId",rs.getInt("classId"));
                map.put("className",rs.getString("className"));
                map.put("updateTime",rs.getString("updateTime"));
                list.add(map);//将map集合放入数组中
            }
            conn.close();//关闭数据库
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
    //重写doPOST方法实现注册信息保存
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //配置请求和响应的字符集为UTF-8
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        HashMap map=new HashMap(15);
        String name=request.getParameter("name");
        map.put("name",name);
        String sex=request.getParameter("sex");
        map.put("sex",sex);
        String nativePlace=request.getParameter("nativePlace");
        map.put("nativePlace",nativePlace);
        String phone=request.getParameter("phone");
        map.put("phone",phone);
        String professionId=request.getParameter("professionId");
        map.put("professionId",professionId);
        String classId=request.getParameter("classId");
        map.put("classId",classId);
        //调用保存学生信息方法
        int num=this.saveStudentInfo(map);
        //创建一个jsnobject对象
        JSONObject jsonobject = new JSONObject();
        if (num>0){
            jsonobject.put("code",num);
            jsonobject.put("msg","保存成功！");
        }else {
            jsonobject.put("code",num);
            jsonobject.put("msg","保存失败！");
        }
        //将jsonobject转化为字符串
        String json = JSON.toJSONString(jsonobject);
        //将json字符串写入响应信息中
        response.getWriter().write(json);
    }
    //保存注册信息
    public int saveStudentInfo(HashMap map){
        int num=0;//表示数据是否保存成功，如果num大于0，则保存成功,否则就是失败
        try {
            //调用连接数据库方法
            ConnDb db = new ConnDb();
            Connection conn = db.dataBaseConnectionTest();
            //构建SQL语句
            String sql1="insert into tb_student_info\n" +
                    "(stuNo,\n" +
                    "name,\n" +
                    "sex,\n" +
                    "nativePlace,\n" +
                    "phone,\n" +
                    "professionId,\n" +
                    "classId\n" +
                    ")VALUES(?,?,?,?,?,?,?)";
            //利用PreparedStatement预处理接口创建对象pstm
            PreparedStatement pstm = conn.prepareStatement(sql1);
            //为占位符?赋值
            pstm.setString(1,map.get("stuNo").toString());
            pstm.setString(2,map.get("name").toString());
            pstm.setString(3,map.get("sex").toString());
            pstm.setString(4,map.get("nativePlace").toString());
            pstm.setString(5,map.get("phone").toString());
            pstm.setInt(6,Integer.parseInt(map.get("professionId").toString()));
            pstm.setInt(7,Integer.parseInt(map.get("classId").toString()));
            //executeUpdate方法执行插入或更新，返回影响的行数,存入num变量
            num = pstm.executeUpdate();
            conn.close();//关闭数据库连接资源
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }
}
